﻿using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing.Printing;
using System.Linq;
using System.Web;

namespace DAL
{
    public class UserService
    {
        //修改客户信息
        public static bool EditClient(UserModel model)
        {
            return DBHelper.ExecuteSql($"update client set name='{model.name}',phone='{model.phone}',address='{model.address}' where id='{model.clientid}'");
        }

        //根据客户号获取用户信息
        public static UserModel GetClientByClientid(string clientid)
        {
            UserModel model = null;
            DataTable dt = DBHelper.GetDataTable($"select * from client where id='{clientid}'");
            if (dt.Rows.Count > 0)
            {
                model = new UserModel();
                model.clientid = dt.Rows[0]["id"].ToString();
                model.name = dt.Rows[0]["name"].ToString();
                model.phone = dt.Rows[0]["phone"].ToString();
                model.address = dt.Rows[0]["address"].ToString();
            }
            return model;
        }

        //删除单个账号（卡号）
        public static bool DeleteAccountByCardID(string cardid)
        {
            return DBHelper.ExecuteSql($"delete account where cardid='{cardid}'");
        }

        //添加用户
        public static bool AddUser(UserModel model)
        {
            if (DBHelper.ExecuteSql($"insert client values('{model.clientid}','{model.name}','{model.phone}','{model.address}')"))
            {
                if (DBHelper.ExecuteSql($"insert account values('{model.cardid}','{model.clientid}',{model.pwd},1,1,GETDATE(),{model.moneynum},1)"))
                {
                    return true;
                }
            }
            return false;
        }

        //根据卡号和密码获取用户信息
        public static UserModel GetUserByCarIDAndPwd(string carID,string pwd)
        {
            UserModel model = null;
            try
            {
                //1.通过存储过程来避免SQL注入
                //DataTable dt = DBHelper.GetDataTable($"exec doLogin '{carID}',{pwd}");

                //DataTable dt = DBHelper.GetDataTable($"select a.*,c.name,c.phone,c.address from account a join client c on a.clientid = c.id where a.cardid = '{carID}' and pwd = '{pwd}'");

                //2.通过参数传递来避免SQL注入
                string sql = "select a.*,c.name,c.phone,c.address from account a join client c on a.clientid = c.id where a.cardid = @cardid and pwd = @pwd";
                DBHelper.initConnection();//打开数据库连接

                DataTable dt = new DataTable();

                //2.1
                //SqlDataAdapter adapter = new SqlDataAdapter(sql, DBHelper.conn);
                //adapter.SelectCommand.Parameters.AddWithValue("cardid", carID);
                //adapter.SelectCommand.Parameters.AddWithValue("pwd", pwd);

                //2.2
                SqlCommand cmd = new SqlCommand(sql, DBHelper.conn);
                cmd.Parameters.AddWithValue("cardid", carID);
                cmd.Parameters.AddWithValue("pwd", pwd);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                adapter.Fill(dt);

                DBHelper.conn.Close();//关闭数据连接

                if (dt.Rows.Count > 0)
                {
                    model = new UserModel();
                    model.cardid = dt.Rows[0]["cardid"].ToString();
                    model.clientid = dt.Rows[0]["clientid"].ToString();
                    model.pwd = dt.Rows[0]["pwd"].ToString();
                    model.moneytype = dt.Rows[0]["moneytype"].ToString();
                    model.serviceid = dt.Rows[0]["serviceid"].ToString();
                    model.startdate = dt.Rows[0]["startdate"].ToString();
                    model.moneynum = double.Parse(dt.Rows[0]["moneynum"].ToString());
                    model.loserecord = bool.Parse(dt.Rows[0]["loserecord"].ToString());
                    model.name = dt.Rows[0]["name"].ToString();
                    model.phone = dt.Rows[0]["phone"].ToString();
                    model.address = dt.Rows[0]["address"].ToString();
                }
            }
            catch (Exception)
            {
                return null;
            }
            return model;
        }

        /// <summary>
        /// 搜索
        /// </summary>
        /// <returns>返回用户列表</returns>
        public static List<UserModel> GetUserModelsBySearch(string st)
        {
            List<UserModel> list = new List<UserModel>();
            DataTable dt = DBHelper.GetDataTable($"select a.*,c.name,c.phone,c.address from account a join client c on a.clientid = c.id where c.name like '%{st}%'");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                UserModel model = new UserModel();
                model.cardid = dt.Rows[i]["cardid"].ToString();
                model.clientid = dt.Rows[i]["clientid"].ToString();
                model.pwd = dt.Rows[i]["pwd"].ToString();
                model.moneytype = dt.Rows[i]["moneytype"].ToString();
                model.serviceid = dt.Rows[i]["serviceid"].ToString();
                model.startdate = dt.Rows[i]["startdate"].ToString();
                model.moneynum = double.Parse(dt.Rows[i]["moneynum"].ToString());
                model.loserecord = bool.Parse(dt.Rows[i]["loserecord"].ToString());
                model.name = dt.Rows[i]["name"].ToString();
                model.phone = dt.Rows[i]["phone"].ToString();
                model.address = dt.Rows[i]["address"].ToString();
                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// 获取用户列表（分页）
        /// </summary>
        /// <returns>返回用户列表</returns>
        public static List<UserModel> GetUserModelsWithPage(int pageIndex, int pageSize)
        {
            List<UserModel> list = new List<UserModel>();
            DataTable dt = DBHelper.GetDataTable($"select top {pageSize} * from user_view where cardid not in (select top (({pageIndex}-1)*{pageSize}) cardid from user_view)");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                UserModel model = new UserModel();
                model.cardid = dt.Rows[i]["cardid"].ToString();
                model.clientid = dt.Rows[i]["clientid"].ToString();
                model.pwd = dt.Rows[i]["pwd"].ToString();
                model.moneytype = dt.Rows[i]["moneytype"].ToString();
                model.serviceid = dt.Rows[i]["serviceid"].ToString();
                model.startdate = dt.Rows[i]["startdate"].ToString();
                model.moneynum = double.Parse(dt.Rows[i]["moneynum"].ToString());
                model.loserecord = bool.Parse(dt.Rows[i]["loserecord"].ToString());
                model.name = dt.Rows[i]["name"].ToString();
                model.phone = dt.Rows[i]["phone"].ToString();
                model.address = dt.Rows[i]["address"].ToString();
                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <returns>返回用户列表</returns>
        public static List<UserModel> GetUserModels()
        {
            List<UserModel> list = new List<UserModel>();
            DataTable dt = DBHelper.GetDataTable("select a.*,c.name,c.phone,c.address from account a join client c on a.clientid = c.id");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                UserModel model = new UserModel();
                model.cardid = dt.Rows[i]["cardid"].ToString();
                model.clientid = dt.Rows[i]["clientid"].ToString();
                model.pwd = dt.Rows[i]["pwd"].ToString();
                model.moneytype = dt.Rows[i]["moneytype"].ToString();
                model.serviceid = dt.Rows[i]["serviceid"].ToString();
                model.startdate = dt.Rows[i]["startdate"].ToString();
                model.moneynum = double.Parse(dt.Rows[i]["moneynum"].ToString());
                model.loserecord = bool.Parse(dt.Rows[i]["loserecord"].ToString());
                model.name = dt.Rows[i]["name"].ToString();
                model.phone = dt.Rows[i]["phone"].ToString();
                model.address = dt.Rows[i]["address"].ToString();
                list.Add(model);
            }
            return list;
        }
    }
}